﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class FaqProvider:DataAccess
    {
        public FaqProvider()
        {
            this.CacheDuration = Globals.Settings.Faq.CacheDuration;
            this.EnableCaching = Globals.Settings.Faq.EnableCaching;
            this.ConnectionString = Globals.Settings.ConnectionString;
        }

        #region IDataReader --> Object of Contact
        protected virtual ContactsDetails GetContactFromReader(IDataReader reader)
        {
            return new ContactsDetails(
                (int)reader["ContactID"],
                (DateTime)reader["AddedDate"],
                (string)reader["AddedBy"],
                (string)reader["Address"],
                (string)reader["Phone"],
                (string)reader["Email"],
                (string)reader["Subject"],
                (string)reader["Question"],
                (DateTime)reader["AnswerDate"],
                (string)reader["AnswerBy"],
                (string)reader["Answer"],
                (bool)reader["Approved"]);
        }

        protected virtual List<ContactsDetails> GetContactCollectionFromReader(IDataReader reader)
        {
            List<ContactsDetails> Contacts = new List<ContactsDetails>();
            while (reader.Read())
                Contacts.Add(GetContactFromReader(reader));
            return Contacts;
        }

        #endregion 

        #region IDataReader --> Object of Support
        protected virtual SupportsDetails GetSupportFromReader(IDataReader reader)
        {
            return new SupportsDetails(
                (int)reader["SupportID"],
                (DateTime)reader["AddedDate"],
                (string)reader["AddedBy"],
                (string)reader["Title"],
                (string)reader["Yahoo"],
                (string)reader["Skype"],
                (bool)reader["ShowYahoo"],
                (bool)reader["ShowSkype"],
                (bool)reader["Visible"]);
        }

        protected virtual List<SupportsDetails> GetSupportCollectionFromReader(IDataReader reader)
        {
            List<SupportsDetails> Supports = new List<SupportsDetails>();
            while (reader.Read())
                Supports.Add(GetSupportFromReader(reader));
            return Supports;
        }

        #endregion 

        #region Contact Method
        public List<ContactsDetails> ContactList()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetContactCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public ContactsDetails ContactGet(int ContactID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetContactFromReader(reader);
                else
                    return null;
            }
        }

        public List<ContactsDetails> ContactGetApproved(bool approved)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsGetApproved", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = approved;
                cn.Open();
                return GetContactCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public int InsertContact(ContactsDetails Contact)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = Contact.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = Contact.AddedBy;
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Contact.Address;
                cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Contact.Phone;
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Contact.Email;
                cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = Contact.Subject;
                cmd.Parameters.Add("@Question", SqlDbType.NText).Value = Contact.Question;
                cmd.Parameters.Add("@AnswerDate", SqlDbType.DateTime).Value = Contact.AnswerDate;
                cmd.Parameters.Add("@AnswerBy", SqlDbType.NVarChar).Value = Contact.AnswerBy;
                cmd.Parameters.Add("@Answer", SqlDbType.NVarChar).Value = Contact.Answer;
                cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = Contact.Approved;
                cmd.Parameters.Add("@ContactID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@ContactID"].Value;
            }
        }

        public bool UpdateContact(ContactsDetails Contact)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Address", SqlDbType.NVarChar).Value = Contact.Address;
                cmd.Parameters.Add("@Phone", SqlDbType.NVarChar).Value = Contact.Phone;
                cmd.Parameters.Add("@Email", SqlDbType.NVarChar).Value = Contact.Email;
                cmd.Parameters.Add("@Subject", SqlDbType.NVarChar).Value = Contact.Subject;
                cmd.Parameters.Add("@Question", SqlDbType.NText).Value = Contact.Question;
                cmd.Parameters.Add("@AnswerDate", SqlDbType.DateTime).Value = Contact.AnswerDate;
                cmd.Parameters.Add("@AnswerBy", SqlDbType.NVarChar).Value = Contact.AnswerBy;
                cmd.Parameters.Add("@Answer", SqlDbType.NVarChar).Value = Contact.Answer;
                cmd.Parameters.Add("@Approved", SqlDbType.Bit).Value = Contact.Approved;
                cmd.Parameters.Add("@ContactID", SqlDbType.Int).Value = Contact.ContactID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool DeleteContact(int ContactID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("ContactsDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ContactID", SqlDbType.Int).Value = ContactID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion

        #region Support Method
        public List<SupportsDetails> SupportList()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsList", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetSupportCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public SupportsDetails SupportGet(int SupportID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsGet", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@SupportID", SqlDbType.Int).Value = SupportID;
                cn.Open();
                IDataReader reader = ExecuteReader(cmd, CommandBehavior.SingleRow);
                if (reader.Read())
                    return GetSupportFromReader(reader);
                else
                    return null;
            }
        }

        public List<SupportsDetails> SupportListVisible()
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsListVisible", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cn.Open();
                return GetSupportCollectionFromReader(ExecuteReader(cmd));
            }
        }

        public int InsertSupport(SupportsDetails Support)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsInsert", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@AddedDate", SqlDbType.DateTime).Value = Support.AddedDate;
                cmd.Parameters.Add("@AddedBy", SqlDbType.NVarChar).Value = Support.AddedBy;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Support.Title;
                cmd.Parameters.Add("@Yahoo", SqlDbType.NVarChar).Value = Support.Yahoo;
                cmd.Parameters.Add("@Skype", SqlDbType.NVarChar).Value = Support.Skype;
                cmd.Parameters.Add("@ShowYahoo", SqlDbType.Bit).Value = Support.ShowYahoo;
                cmd.Parameters.Add("@ShowSkype", SqlDbType.Bit).Value = Support.ShowSkype;
                cmd.Parameters.Add("@Visible", SqlDbType.Bit).Value = Support.Visible;
                cmd.Parameters.Add("@SupportID", SqlDbType.Int).Direction = ParameterDirection.Output;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (int)cmd.Parameters["@SupportID"].Value;
            }
        }

        public bool UpdateSupport(SupportsDetails Support)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsUpdate", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = Support.Title;
                cmd.Parameters.Add("@Yahoo", SqlDbType.NVarChar).Value = Support.Yahoo;
                cmd.Parameters.Add("@Skype", SqlDbType.NVarChar).Value = Support.Skype;
                cmd.Parameters.Add("@ShowYahoo", SqlDbType.Bit).Value = Support.ShowYahoo;
                cmd.Parameters.Add("@ShowSkype", SqlDbType.Bit).Value = Support.ShowSkype;
                cmd.Parameters.Add("@Visible", SqlDbType.Bit).Value = Support.Visible;
                cmd.Parameters.Add("@SupportID", SqlDbType.Int).Value = Support.SupportID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        public bool DeleteSupport(int SupportID)
        {
            using (SqlConnection cn = new SqlConnection(this.ConnectionString))
            {
                SqlCommand cmd = new SqlCommand("SupportsDelete", cn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@SupportID", SqlDbType.Int).Value = SupportID;
                cn.Open();
                int ret = ExecuteNonQuery(cmd);
                return (ret == 1);
            }
        }

        #endregion
    }
}
